/*****************************************************************************
 *   Copyright 2011 ZHUANG Yuan              	                             *
 *   SudokuITS as Final Year Project for BScCS, City University of Hong Kong *
 *                                      									 *
 *   Licensed under the Apache License, Version 2.0 (the "License");         *
 *   you may not use this file except in compliance with the License.        *
 *   You may obtain a copy of the License at                                 *
 *                                                                           *
 *   http://www.apache.org/licenses/LICENSE-2.0                              *
 *                                                                           *
 *   Unless required by applicable law or agreed to in writing, software     *
 *   distributed under the License is distributed on an "AS IS" BASIS,       *
 *   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.*
 *   See the License for the specific language governing permissions and     *
 *   limitations under the License.                                          *
 *****************************************************************************/

package android.sudoku.its.sqlite;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.sudoku.its.student.Student;
import android.util.Log;

/**
 * student table
 * @author 51158282
 *
 */
public class StudentTable {
	// student table variables
	public static final String TABLE_STUDENT = "student";
	public static final String COLUMN_ID = "id";
	public static final String COLUMN_NAME = "name";
	public static final String COLUMN_PASSWORD = "password";
	public static final String COLUMN_GENDER = "gender";
	public static final String COLUMN_AGE = "age";
	public static final String COLUMN_SUDOKU_EXPERIENCE = "sudoku_experience";
	public static final String COLUMN_LEVEL = "level";	// indicate student's Sudoku skill level: 0 - beginner; 1 - intermediate; 2 - advanced
	public static final String COLUMN_EDUCATION_LEVEL = "education_level";
	public static final String COLUMN_SUBJECT = "subject";

	// The following columns keep track of how many times has the student use each strategy.
	public static final String COLUMN_MASTERY_ON_FULL_HOUSE = "mastery_on_full_house";
	public static final String COLUMN_MASTERY_ON_NAKED_SINGLE = "mastery_on_naked_single";
	public static final String COLUMN_MASTERY_ON_HIDDEN_SINGLE = "mastery_on_hidden_single";
	public static final String COLUMN_MASTERY_ON_NAKED_PAIR = "mastery_on_naked_pair";
	public static final String COLUMN_MASTERY_ON_HIDDEN_PAIR = "mastery_on_hidden_pair";
	public static final String COLUMN_MASTERY_ON_LOCKED_CANDIDATES = "mastery_on_locked_candidate";	
	
	// Statement to create student table
	private static final String CREATE_TABLE = "CREATE TABLE "  
			+ TABLE_STUDENT + "("  
			+ COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
			+ COLUMN_NAME + " TEXT NOT NULL, " 
			+ COLUMN_PASSWORD + " TEXT NOT NULL, " 
			+ COLUMN_GENDER + " TEXT NOT NULL, " 
			+ COLUMN_AGE + " INTEGER, " 
			+ COLUMN_SUDOKU_EXPERIENCE + " INTEGER," 
			+ COLUMN_LEVEL + " INTEGER,"
			+ COLUMN_EDUCATION_LEVEL + " TEXT NOT NULL, " 
			+ COLUMN_SUBJECT + " TEXT NOT NULL, " 
			+ COLUMN_MASTERY_ON_FULL_HOUSE + " INTEGER,"
			+ COLUMN_MASTERY_ON_NAKED_SINGLE + " INTEGER,"
			+ COLUMN_MASTERY_ON_HIDDEN_SINGLE + " INTEGER,"
			+ COLUMN_MASTERY_ON_NAKED_PAIR + " INTEGER,"
			+ COLUMN_MASTERY_ON_HIDDEN_PAIR + " INTEGER,"
			+ COLUMN_MASTERY_ON_LOCKED_CANDIDATES + " INTEGER"			
			+ ");";
			
	public static void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_TABLE);
	}
	
	public static void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(StudentTable.class.getName(), "Upgrading database from version "
				+ oldVersion + " to " + newVersion
				+ ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_STUDENT);
		onCreate(db);
	}
	
	/**
	 * Add information about a new student into SQLite database
	 * @param sqliteHandler		SQLite database handler
	 * @param student			Information about the student to be added
	 */
	public void addStudent(SQLiteHandler sqliteHandler, Student student) {
		SQLiteDatabase db = sqliteHandler.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		values.put(COLUMN_NAME, student.getName());
		values.put(COLUMN_PASSWORD, student.getPassword());
		values.put(COLUMN_GENDER, student.getGender());
		values.put(COLUMN_AGE, student.getAge());
		values.put(COLUMN_SUDOKU_EXPERIENCE, student.getSudokuExperience());
		values.put(COLUMN_LEVEL, student.getLevel());
		values.put(COLUMN_EDUCATION_LEVEL, student.getEducationLevel());
		values.put(COLUMN_SUBJECT, student.getSubject());
		values.put(COLUMN_MASTERY_ON_FULL_HOUSE, student.getMasteryOnFullHouse());
		values.put(COLUMN_MASTERY_ON_NAKED_SINGLE, student.getMasteryOnNakedSingle());
		values.put(COLUMN_MASTERY_ON_HIDDEN_SINGLE, student.getMasteryOnHiddenSingle());
		values.put(COLUMN_MASTERY_ON_NAKED_PAIR, student.getMasteryOnNakedPair());
		values.put(COLUMN_MASTERY_ON_HIDDEN_PAIR, student.getMasteryOnHiddenPair());
		values.put(COLUMN_MASTERY_ON_LOCKED_CANDIDATES, student.getMasteryOnLockedCandidates());
		
		db.insert(TABLE_STUDENT, null, values);
		db.close();
	}
	
	/**
	 * Get student information by id
	 * @param sqliteHandler		SQLite database handler
	 * @param id				Student id
	 * @return					A Student object containing student information 
	 */
	public Student getStudentById(SQLiteHandler sqliteHandler, int id) {
		SQLiteDatabase db = sqliteHandler.getWritableDatabase();
		
		Cursor cursor = db.query(TABLE_STUDENT, 
				new String[] {COLUMN_ID, COLUMN_NAME, COLUMN_PASSWORD, COLUMN_GENDER, 
				COLUMN_AGE, COLUMN_SUDOKU_EXPERIENCE, COLUMN_LEVEL, COLUMN_EDUCATION_LEVEL, COLUMN_SUBJECT, 
				COLUMN_MASTERY_ON_FULL_HOUSE, COLUMN_MASTERY_ON_NAKED_SINGLE, COLUMN_MASTERY_ON_HIDDEN_SINGLE, 
				COLUMN_MASTERY_ON_NAKED_PAIR, COLUMN_MASTERY_ON_HIDDEN_PAIR, COLUMN_MASTERY_ON_LOCKED_CANDIDATES}, 
				COLUMN_ID + "=?", 
				new String[] {String.valueOf(id)}, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();
		
		Student student = new Student(Integer.parseInt(cursor.getString(0)), 
				cursor.getString(1), cursor.getString(2), cursor.getString(3), 
				Integer.parseInt(cursor.getString(4)), Integer.parseInt(cursor.getString(5)), Integer.parseInt(cursor.getString(6)), 
				cursor.getString(7), cursor.getString(8), 
				Integer.parseInt(cursor.getString(9)), Integer.parseInt(cursor.getString(10)), 
				Integer.parseInt(cursor.getString(11)), Integer.parseInt(cursor.getString(12)), 
				Integer.parseInt(cursor.getString(13)), Integer.parseInt(cursor.getString(14)));
		
		db.close();
		return student;
	}
	
	/**
	 * Get student information by name
	 * @param sqliteHandler		SQLite database handler
	 * @param name				Student name
	 * @return					A Student object containing student information 
	 */	
	public Student getStudentByName(SQLiteHandler sqliteHandler, String name) {
		SQLiteDatabase db = sqliteHandler.getWritableDatabase();
		
		Cursor cursor = db.query(TABLE_STUDENT, 
				new String[] {COLUMN_ID, COLUMN_NAME, COLUMN_PASSWORD, COLUMN_GENDER, 
						COLUMN_AGE, COLUMN_SUDOKU_EXPERIENCE, COLUMN_LEVEL, COLUMN_EDUCATION_LEVEL, COLUMN_SUBJECT, 
						COLUMN_MASTERY_ON_FULL_HOUSE, COLUMN_MASTERY_ON_NAKED_SINGLE, COLUMN_MASTERY_ON_HIDDEN_SINGLE, 
						COLUMN_MASTERY_ON_NAKED_PAIR, COLUMN_MASTERY_ON_HIDDEN_PAIR, COLUMN_MASTERY_ON_LOCKED_CANDIDATES},  
				COLUMN_NAME + "=?", 
				new String[] {name}, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();
		
		Student student = new Student(Integer.parseInt(cursor.getString(0)), 
				cursor.getString(1), cursor.getString(2), cursor.getString(3), 
				Integer.parseInt(cursor.getString(4)), Integer.parseInt(cursor.getString(5)), Integer.parseInt(cursor.getString(6)), 
				cursor.getString(7), cursor.getString(8), 
				Integer.parseInt(cursor.getString(9)), Integer.parseInt(cursor.getString(10)), 
				Integer.parseInt(cursor.getString(11)), Integer.parseInt(cursor.getString(12)), 
				Integer.parseInt(cursor.getString(13)), Integer.parseInt(cursor.getString(14)));
		
		db.close();
		return student;
	}
	
	/**
	 * Update student information by id
	 * @param sqliteHandler		SQLite database handler
	 * @param student			Information about the student to be added
	 * @return					The number of rows affected					
	 */
	public int updateStudentById(SQLiteHandler sqliteHandler, Student student) {
		SQLiteDatabase db = sqliteHandler.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(COLUMN_NAME, student.getName());
		values.put(COLUMN_PASSWORD, student.getPassword());
		values.put(COLUMN_GENDER, student.getGender());
		values.put(COLUMN_AGE, student.getAge());
		values.put(COLUMN_SUDOKU_EXPERIENCE, student.getSudokuExperience());
		values.put(COLUMN_LEVEL, student.getLevel());
		values.put(COLUMN_EDUCATION_LEVEL, student.getEducationLevel());
		values.put(COLUMN_SUBJECT, student.getSubject());
		values.put(COLUMN_MASTERY_ON_FULL_HOUSE, student.getMasteryOnFullHouse());
		values.put(COLUMN_MASTERY_ON_NAKED_SINGLE, student.getMasteryOnNakedSingle());
		values.put(COLUMN_MASTERY_ON_HIDDEN_SINGLE, student.getMasteryOnHiddenSingle());
		values.put(COLUMN_MASTERY_ON_NAKED_PAIR, student.getMasteryOnNakedPair());
		values.put(COLUMN_MASTERY_ON_HIDDEN_PAIR, student.getMasteryOnHiddenPair());
		values.put(COLUMN_MASTERY_ON_LOCKED_CANDIDATES, student.getMasteryOnLockedCandidates());
		
		int numOfRowAffected = db.update(TABLE_STUDENT, values, COLUMN_ID + "=?", new String[] {String.valueOf(student.getId())});
		db.close();
		return numOfRowAffected;
	}
	
	
	
}
